
using System;
using System.Data;

/// <summary>
/// Summary description for DirectServiceActionTaken
/// </summary>
public class DirectServiceActionTaken
{
    private ConnectionBase sgbdr;
    public DirectServiceActionTaken()
    {
      sgbdr= new ConnectionBase();
    }
    public DataTable  GetActionTakenDiplayed(string whereclause ,string orderclause)
    {
        string sql="SELECT EMPLOYEE.EMPLOYEENAME, EMPLOYEE.USERNAME, TASKINWORKORDER.TASKINWORKORDERID, " 
				+ "TASKINWORKORDER.WORKORDERID, REPARATION.PLANNINGID, PLANNING.EMPLOYEEID, "
				+ "convert(varchar, REPARATION.STARTDATE, 101) as STARTDATE, " 
				+ "convert(varchar, REPARATION.ENDDATE, 101) as ENDDATE, "
				+ "REPARATION.DURATIONINMINUT, REPARATION.TASKSTATUSID, "
				+ "REPARATION.REPARATIONID, "
				+ "PLANNING.TASKINWORKORDERID, TASKSTATUS.TASKSTATUS , "
				+ "REPARATIONEQUIPEMENT.DESCRIPTION "
				+ "FROM REPARATION INNER JOIN " 
				+ "PLANNING ON REPARATION.PLANNINGID = PLANNING.PLANNINGID INNER JOIN "
				+ "TASKSTATUS ON REPARATION.TASKSTATUSID = TASKSTATUS.TASKSTATUSID INNER JOIN "
				+ "EMPLOYEE ON PLANNING.EMPLOYEEID = EMPLOYEE.EMPLOYEEID INNER JOIN "
				+ "REPARATIONEQUIPEMENT ON REPARATION.REPARATIONID = REPARATIONEQUIPEMENT.REPARATIONID INNER JOIN "
				+ "TASKINWORKORDER ON PLANNING.TASKINWORKORDERID = TASKINWORKORDER.TASKINWORKORDERID " ;
        sql += whereclause;
        return sgbdr.consultSQL(sql).Tables[0];    
    }
    public DataTable GetSparePartOfActionTakenDisplayed(string whereclause,string orderclause)
    {
        string sql="SELECT REPARATIONSPAREPART.DESCRIPTION, convert(varchar, REPARATIONSPAREPART.DATE, 101) as DATE, " 
				+ "(convert(varchar, REPARATIONSPAREPART.REPARATIONSPAREPARTID) + convert(varchar, ' ') + convert(varchar, PROBLEMREPARATIONSPAREPART.PROBLEMID)) as argument, "
				+ "REPARATIONSPAREPART.REPARATIONSPAREPARTID, PROBLEMREPARATIONSPAREPART.PROBLEMID, PROBLEM.PROBLEM, "
				+ "REPARATIONSPAREPART.REPARATIONID, "
				+ "SPAREPART.MODELID, TYPEEQUIPMENT.TYPEEQUIPEMENTID, TYPEEQUIPMENT.TYPEEQUIPMENT, "
				+ "STATUSREPARATION.STATUS "
				+ "FROM REPARATIONSPAREPART INNER JOIN " 
				+ "SPAREPART ON REPARATIONSPAREPART.SPAREPARTID = SPAREPART.SPAREPARTID INNER JOIN "
				+ "MODEL ON SPAREPART.MODELID = MODEL.MODELID INNER JOIN "
				+ "[IN] ON [IN].modelID = MODEL.MODELID INNER JOIN "
				+ "TYPEEQUIPMENT ON [IN].typeEquipementID = TYPEEQUIPMENT.TYPEEQUIPEMENTID INNER JOIN "
				+ "STATUSREPARATION ON REPARATIONSPAREPART.STATUSID = STATUSREPARATION.STATUSID INNER JOIN " 
				+ "PROBLEMREPARATIONSPAREPART ON REPARATIONSPAREPART.REPARATIONSPAREPARTID = PROBLEMREPARATIONSPAREPART.REPARATIONSPAREPARTID "
				+ "INNER JOIN  PROBLEM ON PROBLEMREPARATIONSPAREPART.PROBLEMID = PROBLEM.PROBLEMID "
				+ "WHERE REPARATIONSPAREPART.REPARATIONID = " + 1 ;
            sql += whereclause;
            return sgbdr.consultSQL(sql).Tables[0];   
 
    }
    public DataTable GetSendMail(string whereclause, string orderclause)
    {
        string sql="SELECT DISTINCT TASKSTATUS.TASKSTATUS, CONVERT(varchar, REPARATION.STARTDATE, 101) AS startdate, CONVERT(varchar, REPARATION.endDATE, 101) AS enddate, REPARATION.DURATIONINMINUT," 
                  +" TASKINWORKORDER.WORKORDERID, TASKINWORKORDER.CODENUMBER, EMPLOYEE.EMPLOYEENAME, "
                  +" EMPLOYEE.EMPLOYEEEMAILADRESS"
                  +" FROM    TASKINWORKORDER INNER JOIN"
                  +" PLANNING ON TASKINWORKORDER.TASKINWORKORDERID = PLANNING.TASKINWORKORDERID INNER JOIN"
                  +" TASKSTATUS INNER JOIN"
                  +" REPARATION ON TASKSTATUS.TASKSTATUSID = REPARATION.TASKSTATUSID ON PLANNING.PLANNINGID = REPARATION.PLANNINGID INNER JOIN"
                  +" WORKORDER ON TASKINWORKORDER.WORKORDERID = WORKORDER.WORKORDERID INNER JOIN"
                  +" EMPLOYEE ON WORKORDER.EMPLOYEEID = EMPLOYEE.EMPLOYEEID CROSS JOIN"
                  +" PLANNING PLANNING_1"
				  +" WHERE     (REPARATION.REPARATIONID = "+ 1 +")";
        sql += whereclause;
        return sgbdr.consultSQL(sql).Tables[0];   
 
    }
    public DataTable GetAddNewActionTaken(int planningId)
    {

        string sql= "SELECT TASKINWORKORDER.CODENUMBER, "
					+" TASKINWORKORDER.WORKORDERID, TASKINWORKORDER.TASKINWORKORDERID "
					+" FROM PLANNING INNER JOIN TASKINWORKORDER "
					+" ON PLANNING.TASKINWORKORDERID = TASKINWORKORDER.TASKINWORKORDERID "
					+" WHERE PLANNING.PLANNINGID = " + planningId;
        return sgbdr.consultSQL(sql).Tables[0]; 
  
    }
    public DataTable GetEditActionTaken(int actionTakenId)
    {
        string sql = "SELECT TASKINWORKORDER.CODENUMBER, EQUIPEMENT.DEMAGED, "
                + " TASKINWORKORDER.WORKORDERID, TASKINWORKORDER.TASKINWORKORDERID, "
                + " REPARATION.STARTDATE, REPARATION.ENDDATE, REPARATION.PLANNINGID, "
                + " REPARATION.DURATIONINMINUT, REPARATION.TASKSTATUSID, REPARATION.CostPerReparation, "
                + "REPARATIONEQUIPEMENT.DESCRIPTION "
                + " FROM PLANNING INNER JOIN TASKINWORKORDER "
                + " ON PLANNING.TASKINWORKORDERID = TASKINWORKORDER.TASKINWORKORDERID "
                + " INNER JOIN EQUIPEMENT ON TASKINWORKORDER.CODENUMBER = EQUIPEMENT.CODENUMBER "
                + " INNER JOIN REPARATION ON REPARATION.PLANNINGID = PLANNING.PLANNINGID "
                + " INNER JOIN REPARATIONEQUIPEMENT ON REPARATION.REPARATIONID = REPARATIONEQUIPEMENT.REPARATIONID "
                + " WHERE REPARATION.REPARATIONID = " + actionTakenId;
        return sgbdr.consultSQL(sql).Tables[0];
    }
    public void InsertActionTaken(int planningId,int taskStatusId,string startDate,string endDate,int durationinminut,float costPerReparation)
    {

        string sql = "INSERT INTO reparation(PLANNINGID, TASKSTATUSID, [STARTDATE], [ENDDATE], DURATIONINMINUT,CostPerReparation)"
					+ " VALUES(planningId,taskStatusId ,CONVERT(datetime,startDate, 101) ,"
                    + "CONVERT(datetime,endDate, 101) ,durationinminut ,costPerReparation)";
        sgbdr.executeSQL(sql);

    }
}
